// $codepro.audit.disable packageNamingConvention

/** Packages */
package edu.gatech.team404.dal;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.gatech.team404.Account;
import edu.gatech.team404.Team;
import edu.gatech.team404.tool.Log;

/**
 * A Data Access Layer for TeamDAL.
 * @author Michael Dandy
 * @version 1.0
 */
public class TeamDAL extends IDatabase {
	/** Class tag for logging purpose */
	private final String TAG = "TeamDAL";
	
	/** The team table name that this DAL is associated with */
	private final String TEAM_TABLE = "TeamTable";
	
	/** The team lab table name that this DAL is associated with */
	private final String TEAM_LAB_TABLE = "TeamIDTable";
	
	/**
	 * Create a team.
	 * @param team the team to be created
	 * @return true on success or false otherwise
	 */
	public boolean createTeam (Team team) {
		final List<Account> members = team.getMembers();
		boolean ret = true;
		String query = "";
		
		for (Account member : members) {
			query = "INSERT INTO " + TEAM_TABLE + " VALUES (" 
					+ "\'" + team.getTeamID() + "\', " 
					+ "\'" + team.getName() + "\', " 
					+ "\'" + member.getUsername() + "\')";
			Log.info(TAG, "Adding a member for the team ...");
			Log.info(TAG, query);
			ret &= executeUpdate(query);
		}
		
		query = "INSERT INTO " + TEAM_LAB_TABLE + " VALUES (" 
				+ "\'" + team.getTeamID() + "\', " 
				+ "\'" + team.getName() + "\', " 
				+ "\'" + team.getLabID() + "\', " 
				+ "\'" + team.getOverride() + "\')";
		Log.info(TAG, "Adding a team to the lab ...");
		Log.info(TAG, query);
		ret &= executeUpdate(query);
		
		return ret;
	}
	
	/**
	 * Get team by the specified team ID.
	 * @param teamID the ID of the team
	 * @return the team with the specified ID
	 */
	public Team getTeamById (String teamID) {
		String query = "SELECT * FROM " +  TEAM_LAB_TABLE
							+ " WHERE TeamID = \'" + teamID + "\'";
		ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving labID associated with the team ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				if (result.next()) {
					final AccountDAL dal = new AccountDAL();
					
					final String name = result.getString(2);
					final String labID = result.getString(3);
					final int override = result.getInt(4);
					final List<Account> members = new ArrayList<Account>(); 
					
					query = "SELECT * FROM " +  TEAM_TABLE
							+ " WHERE TeamID = \'" + teamID + "\'";
					result = super.executeQuery(query);
					
					Log.info(TAG, "Retrieving a team ...");
					Log.info(TAG, query);

					while(result.next()) {
						members.add(dal.getAccount(result.getString(3)));
					}

					return new Team (labID, teamID, name, override, members);
				}
			} 
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Get team by the specified lab ID, team ID, and name.
	 * @param labId the ID of the lab associated with this team
	 * @param teamId the ID of team team
	 * @param name the name of the team
	 * @return the team with the specified ID
	 */
	public Team getTeam (String labId, String teamId, String name) {
		final AccountDAL dal = new AccountDAL();
		final List<Account> members = new ArrayList<Account>(); 
			
		String query = "SELECT * FROM " +  TEAM_LAB_TABLE
						+ " WHERE LabID = \'" + labId + "\'";;
		ResultSet result = super.executeQuery(query);
		
		if (result != null) {
			try {
				if (checkEntry (result, 1, teamId)) {
					final int override = result.getInt(4);
					
					query = "SELECT * FROM " +  TEAM_TABLE
							+ " WHERE TeamID = \'" + teamId + "\'";
					result = super.executeQuery(query);
									
					Log.info(TAG, "Retrieving a team ...");
					Log.info(TAG, query);

					while(result.next()) {
						members.add(dal.getAccount(result.getString(3)));
					}
					return new Team (labId, teamId, name, override, members);
				}
			}
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Check if specific entry is in the result set
	 * @param result the result set
	 * @param columnIndex the column of result set
	 * @param entry the entry to be searched
	 * @return true if specific entry is in the result set or false otherwise
	 */
	private boolean checkEntry (ResultSet result, int columnIndex, String entry) {
		try {
			while (result.next()) {
				if (result.getString(columnIndex).equalsIgnoreCase(entry)) {
					return true;
				}
			}
		} 
		catch (SQLException e) {
			Log.error(TAG, e);
		}
		return false;
	}
	
	/**
	 * Get the team associated with the specified lab and name.
	 * @param labID the lab ID which the team is associated with
	 * @param name the name of the team
	 * @return the team associated with the specified lab and name
	 */
	public Team getTeamByName (String labID, String name) {
		final String query = "SELECT * FROM " + TEAM_LAB_TABLE 
							+ " WHERE Name = \'" + name + "\' AND"
							+ " LabID = \'" + labID + "\'";
		final ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving team ID ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				if (result.next()) {
					return getTeam (labID, result.getString(1), name);
				}
			} 
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Get the teams associated with the specified lab.
	 * @param labID the lab ID which the team is associated with
	 * @return the team associated with the specified lab
	 */
	public List<Team> getTeamsInLab (String labID) {
		final List<Team> teams= new ArrayList<Team> ();
		final String query = "SELECT * FROM " + TEAM_LAB_TABLE 
							+ " WHERE LabID = \'" + labID + "\'";
		final ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving team ID ...");
		Log.info(TAG, query);
		
		try {
			while(result.next()) {
				teams.add(getTeam(labID, result.getString(1), result.getString(2)));
			}
			return teams;
		}
		catch (SQLException e) {
			Log.error(TAG, e);
			return null;
		}
	}
	
	/**
	 * Update the team information. Only name and override status can
	 * be updated.
	 * @param team the team to be updated
	 * @return true on success or false otherwise
	 */
	public boolean updateTeam (Team team) {
		boolean ret = true;
		String query = "UPDATE " + TEAM_TABLE + " SET ";
		String update = "";

		final Team old = getTeamById (team.getTeamID());
		
		if (!team.getName().isEmpty() && 
			!old.getName().equalsIgnoreCase(team.getName())) {
			update += "Name=\'" + team.getName() + "\'";
			
			query += update;
			query += " WHERE TeamID=\'" + team.getTeamID() + "\'";
			
			Log.info(TAG, "Updating team table ...");
			Log.info(TAG, query);
			ret &= super.executeUpdate(query);
			
			if (ret) {
				query = "UPDATE " + TEAM_LAB_TABLE + " SET ";
				query += update;
				query += " WHERE TeamID=\'" + team.getTeamID() + "\'";
				
				Log.info(TAG, "Updating team lab table ...");
				Log.info(TAG, query);
				ret &= super.executeUpdate(query);
			}
		}
		
		if (team.getOverride() != old.getOverride()) {
			query = "UPDATE " + TEAM_LAB_TABLE + " SET Override="
					+ team.getOverride();
			
			Log.info(TAG, "Overriding team table ...");
			Log.info(TAG, query);
			
			ret &= super.executeUpdate(query);
		}

		if (ret && team.getMembers() != null) {
			List<Account> oldMembers = old.getMembers();
			List<Account> newMembers = team.getMembers();
			query = "UPDATE " + TEAM_TABLE + " SET ";
			
			for (int i = 0; i < newMembers.size(); i++) {
				update = "Member=\'" + newMembers.get(i).getUsername() + "\'";
				
				Log.info(TAG, "Updating team member ...");
				Log.info(TAG, query + update + " WHERE TeamID=\'" 
						+ team.getTeamID() + "\'");
				ret &= super.executeUpdate(query + update + " WHERE TeamID=\'" 
						+ team.getTeamID() + "\' AND Member=\'" 
						+ oldMembers.get(i).getUsername()  + "\'");
			}
		}
		
		return ret;
	}
	
	/**
	 * Add member to the team.
	 * @param username the username to be added
	 * @param team the team
	 * @return true on success or false otherwise
	 */
	public boolean addMember (String username, Team team) {
		final String query = "INSERT INTO " + TEAM_TABLE + " VALUES (" 
							+ "\'" + team.getTeamID() + "\', " 
							+ "\'" + team.getName() + "\', " 
							+ "\'" + username + "\')";
		
		Log.info(TAG, "Adding member ...");
		Log.info(TAG, query);
		return super.executeUpdate(query);
	}
	
	/**
	 * Remove the specified member from the team
	 * @param username the username to be removed
	 * @param team the team
	 * @return true on success or false otherwise
	 */
	public boolean removeMember (String username, Team team) {
		final String query = "DELETE FROM " + TEAM_TABLE 
							+ " WHERE TeamID=\'" + team.getTeamID() 
							+ "\' AND Member=\'" + username + "\'"; 
		
		Log.info(TAG, "Removing member ...");
		Log.info(TAG, query);
		return super.executeUpdate(query);
	}
	
	/**
	 * Remove the specified team
	 * @param teamId the id of the team
	 * @return true on success or false otherwise
	 */
	public boolean removeTeam (String teamId) {
		boolean ret = true;
		String query = "DELETE FROM " + TEAM_TABLE 
						+ " WHERE TeamID=\'" + teamId + "\'";
		
		Log.info(TAG, "Removing account from team table ...");
		Log.info(TAG, query);
		ret &= super.executeUpdate(query);
		
		query = "DELETE FROM " + TEAM_LAB_TABLE 
				+ " WHERE TeamID=\'" + teamId + "\'";

		Log.info(TAG, "Removing account from team lab table ...");
		Log.info(TAG, query);
		ret &= super.executeUpdate(query);
		
		return ret;
	}
	
	/**
	 * Close SQL connection
	 * @return true on success or false otherwise
	 */
	public boolean close() {
		return super.close();
	}
}
